package cn.com.hsm.tools;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.util.HashMap;
import java.util.Map;

public class ExcelUtil {
    public static final String INSERTSQL = "INSERT INTO CODE_BUSITERMCODE (CODE,NAME,PARENTCODE,COMMONSIGN,POSITIONSIGN,CHOSIGN,BIZTYPE,INDUSTYPECODE,POSITIONNAME,TIPS,OTHERTYPE,RESON,INDUSTYPENAME,FLAG,ENTTYPE,ENTPROPERTY,ISBLONGINDUSTRIAL,MATTERSCODE,MATTERSNAME,LAWREGULATION,CATEGORY,HEADING,MATTERSTYPE) VALUES ('%s','%s',null,null,'%s','Y',null,'%s','%s',null,null,null,'%s','1',null,null,'1','','',null,'%s','%s','');";

    public static void main(String[] args) {

        System.out.println(findNumber("666zgh"));
        Workbook wb = getExcel("D:\\（4.30）1表最新版《经营范围登记规范表述目录》.xlsx");
        if (wb == null) {
            System.out.println("文件读入出错");
        }else {
            analyzeExcel(wb);
        }

    }

    public static Workbook getExcel(String filePath) {
        Workbook wb = null;
        File file = new File(filePath);
        if (!file.exists()) {
            System.out.println("文件不存在");
            wb = null;
        } else {
            String fileType = filePath.substring(filePath.lastIndexOf("."));
            try {
                InputStream is = new FileInputStream(filePath);
                if (".xls".equals(fileType)) {
                    wb = new HSSFWorkbook(is);
                } else if (".xlsx".equals(fileType)) {
                    wb = new XSSFWorkbook(is);
                } else {
                    System.out.println("格式不正确");
                    wb = null;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return wb;
    }

    public static void analyzeExcel(Workbook wb) {
        Sheet sheet = wb.getSheetAt(0);
        int rowNum = sheet.getLastRowNum();
        BufferedWriter bw = null;
        try {
            bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(new File("D:/结果.txt")),"UTF-8"));
        } catch (Exception e) {
            System.err.println("write errors :" + e);
        }
        for (int i = 2; i <= rowNum; i++) {
            Row row = sheet.getRow(i);
            int colNum = row.getLastCellNum();
            String code = row.getCell(0).toString();
            String name = row.getCell(2).toString();
            String value9 = row.getCell(8).toString();
            String positionSign = "1";
            if (StringUtils.equals("前置事项",value9)) {
                positionSign = "3";
            } else if (StringUtils.equals("后置事项",value9)) {
                positionSign = "2";
            }
            StringBuilder industypecodeSb = new StringBuilder();
            StringBuilder industypeNameSb = new StringBuilder();
            String headding = "";
            String value5 =  row.getCell(4).toString();
            String[] value5Array = value5.split("；");
            for (int j = 0; j < value5Array.length; j++) {
                industypecodeSb.append(findNumber(value5Array[j]));
                industypeNameSb.append(value5Array[j].replace(findNumber(value5Array[j]), ""));
                if (StringUtils.isNotBlank(findNumber(value5Array[j])) && !headding.contains(findNumber(value5Array[j]).substring(0,2))) {
                    headding += findNumber(value5Array[j]).substring(0,2) + ";";
                }
                if (j != value5Array.length -1) {
                    industypecodeSb.append(";");
                    industypeNameSb.append(";");
                }
            }
            String industypecode = industypecodeSb.toString();
            String positionName = value9;
            String industypename = industypeNameSb.toString();
            String category = row.getCell(0).toString().substring(0,1);
            if (StringUtils.isNotBlank(headding)) {
                headding = headding.substring(0, headding.length() -1);
            }
            System.out.println(code + "----------------" + industypecode + "-------" + headding + "----------" + industypename);
//            for (int j = 0; j < colNum; j++) {
//                Cell cell = row.getCell(j);
//                if (cell == null){
//                    System.out.print("null     ");
//                }else{
//                    System.out.print(cell.toString() + "     ");
//                }
//
//            }
            String resultSql = String.format(ExcelUtil.INSERTSQL, code, name, positionSign, industypecode, positionName,industypename, category, headding);
            try {
                bw.write(resultSql);
                bw.newLine();
            } catch (Exception e) {
                System.err.println("write errors :" + e);
            }
        }
        try {
            bw.close();
        } catch (Exception e) {
            System.err.println("write errors :" + e);
        }
    }

    public static String findNumber(String s){
        String result = "";
        for(int i = 0; i < s.length(); i++){
            int code = s.codePointAt(i);
            if(code >= 48 && code <= 57){
                result += (char)code;
            }
        }
        return result;
    }

//    package help;
//
//
//
//    public class TXTParseUtils {
//
//        private static final Integer ONE = 1;
//
//        public static void main(String[] args) {
//            Map<String, Integer> map = new HashMap<String, Integer>();
//
//        /* 读取数据 */
//            try {
//                BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(new File("D:/报销.txt")),
//                        "UTF-8"));
//                String lineTxt = null;
//                while ((lineTxt = br.readLine()) != null) {
//                    String[] names = lineTxt.split(",");
//                    for (String name : names) {
//                        if (map.keySet().contains(name)) {
//                            map.put(name, (map.get(name) + ONE));
//                        } else {
//                            map.put(name, ONE);
//                        }
//                    }
//                }
//                br.close();
//            } catch (Exception e) {
//                System.err.println("read errors :" + e);
//            }
//
//
//        }
//    }


}
